-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE NTDBNEW
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sushil Mittal
-- Create date: 
-- Description:	
-- =============================================
IF OBJECT_ID('sp_NTDBNEW_ExtractRelevantCols') IS NOT NULL
    DROP PROCEDURE sp_NTDBNEW_ExtractRelevantCols

GO
CREATE PROCEDURE sp_NTDBNEW_ExtractRelevantCols 
	-- Add the parameters for the stored procedure here
	--@TableName NVARCHAR(100)
AS
BEGIN
	-- Insert statements for procedure here	SET NOCOUNT ON;
SELECT

/*AISCCODE     */ AISCCODE.INC_KEY, AISCCODE.PREDOT, AISCCODE.SEVERITY, --BODYREGION is obtained FROM AISCCODE.PREDOT in Perl
/*DEMO         */ DEMO.AGE, DEMO.GENDER,
/*DCODE        */ DCODE.DCODE,
/*DISCHARGE    */ DISCHARGE.LOSDAYS, DISCHARGE.HOSPDISP,
/*ECODE        */ ECODE.ECODE, ECODE.ECODE2,
/*VITALS       */ VITALS.SBP, VITALS.RR, VITALS.PULSE, VITALS.GCSEYE, VITALS.GCSVERB, VITALS.GCSMOT, VITALS.GCSTOT,
/*ECODEDES     */ ECODEDES.INJTYPE,
/*ED           */ ED.EDDISP, ED.LOCATION, ED.YOINJ

FROM

AISCCODE
INNER JOIN
DEMO
ON DEMO.INC_KEY = AISCCODE.INC_KEY
INNER JOIN 
DCODE
ON DEMO.INC_KEY = DCODE.INC_KEY
INNER JOIN
DISCHARGE
ON DEMO.INC_KEY = DISCHARGE.INC_KEY
INNER JOIN
ECODE
ON DEMO.INC_KEY = ECODE.INC_KEY
INNER JOIN
ECODEDES
ON ECODE.ECODE = ECODEDES.ECODE
INNER JOIN
ED
ON DEMO.INC_KEY = ED.INC_KEY
INNER JOIN
VITALS
ON DEMO.INC_KEY = VITALS.INC_KEY

--Filter based on Age first
WHERE DEMO.AGE <= 14 AND DEMO.AGE >= 0

AND AISCCODE.INC_KEY IS NOT NULL																						--MISSING
AND AISCCODE.PREDOT IS NOT NULL																							--MISSING
AND AISCCODE.SEVERITY IS NOT NULL					AND AISCCODE.SEVERITY != 9											--MISSING

AND DEMO.AGE IS NOT NULL																								--MISSING
AND DEMO.GENDER IS NOT NULL	 																							--MISSING
AND DEMO.GENDER != 'Not Applicable BIU 1'																				--MISSING
AND DEMO.GENDER != 'Not Known BIU 2'																					--MISSING
AND DEMO.GENDER != 'Not Known/Not Recorded BIU 2'																		--MISSING
AND DEMO.GENDER != 'Not Recorded BIU 3'																					--MISSING

AND DCODE.DCODE IS NOT NULL							AND DCODE.DCODE != 'null'											--MISSING

AND DISCHARGE.LOSDAYS IS NOT NULL					AND DISCHARGE.LOSDAYS >= 0											--MISSING
--DISCHARGE.HOSPDISP is first merged with ED.EDDISP and then filtered in Perl.

--ECODE.ECODE and ECODE.ECODE2 are merged and filtered in Perl.

AND VITALS.SBP IS NOT NULL							AND VITALS.SBP != 'null'			AND VITALS.SBP >= 0				--MISSING
AND VITALS.RR IS NOT NULL							AND VITALS.RR != 'null'				AND VITALS.RR >= 0				--MISSING
AND VITALS.PULSE IS NOT NULL						AND VITALS.PULSE != 'null'			AND VITALS.PULSE >= 0			--MISSING
AND VITALS.VSTYPE != 'EMS'
--ALL 4 GCS are filtered in Perl.

AND ECODEDES.INJTYPE IS NOT NULL																						--MISSING
AND ECODEDES.INJTYPE != 'Activity Code - Not Valid as a Primary E-Code'													--MISSING
AND ECODEDES.INJTYPE != 'null'																							--MISSING
AND ECODEDES.INJTYPE != 'NULL'																							--MISSING
AND ECODEDES.INJTYPE != 'Burn'																							--BURN/ NOT MISSING
AND ECODEDES.INJTYPE != 'Other/unspecified'																				--NOT MISSING

--ED.EDDISP is first merged with DISCHARGE.HOSPDISP and then filtered in Perl.

AND ED.LOCATION IS NOT NULL
AND ED.LOCATION != 'Not Applicable BIU 1'																				--MISSING
AND ED.LOCATION != 'Not Known BIU 2'																					--MISSING
AND ED.LOCATION != 'Not Known/Not Recorded BIU 2'																		--MISSING
AND ED.LOCATION != 'Not Recorded BIU 3'																					--MISSING
AND ED.LOCATION != 'Unspecified'																						--MISSING

END
GO
